package pms.common.dao;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.sql.ResultSet;

import org.springframework.core.io.FileSystemResource;
import org.springframework.jdbc.LobRetrievalFailureException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.core.support.AbstractLobStreamingResultSetExtractor;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.util.FileCopyUtils;

import pms.common.value.QaListValue;

public class QaDetailDaoImpl implements QaDetailDao {

	private JdbcTemplate jdbcTemplate;
	private LobHandler lobHandler;
	private String filename;
	private InputStream is;
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	public SqlRowSet getListdetailData(String NO) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		SqlRowSet l_sqs = jdbcTemplate.queryForRowSet("SELECT * FROM TBL_QA_LIST WHERE QA_NO = '"+NO+"'");
		return l_sqs;
	}
	
	public void getBlobData(String NO,String direc) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		final String QA_FILE_NM = direc+"_NM";
		final String QA_FILE = direc;
		String sql = "SELECT "+QA_FILE_NM+","+QA_FILE+" FROM TBL_QA_LIST WHERE QA_NO = ?";
		System.out.println("sql :"+sql);
		jdbcTemplate.query(
		    	sql, new Object[] {NO},
		    	new AbstractLobStreamingResultSetExtractor() {    					
		    		protected void handleNoRowFound() throws LobRetrievalFailureException {
		                  System.out.println("Not Found result!");
		    			}
		   public void streamData(ResultSet rs) throws SQLException, IOException {
			   				filename = rs.getString(QA_FILE_NM);
			   				is = rs.getBinaryStream(QA_FILE);
		    					}
		    				});
	}
	
	
	public void updateListdetailData(final File file, final String fileName, final QaListValue detailList, final String limitDate, final String currentDate, final String confirmDate) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {

		System.out.println("fileName :"+fileName);
		System.out.println("detailList.getStatus() :"+detailList.getStatus());
		System.out.println("detailList.getEmergencyDegree() :"+detailList.getEmergencyDegree());
		System.out.println("detailList.getToIAWaSeSaKi() :"+detailList.getToIAWaSeSaKi());
		System.out.println("detailList.getSelBanGo() :"+detailList.getSelBanGo());
		System.out.println("detailList.getNaiYo() :"+detailList.getNaiYo());
		System.out.println("detailList.getNaiYo() :"+detailList.getNaiYo());
		System.out.println("limitDate :"+limitDate);
		System.out.println("currentDate :"+currentDate);
		System.out.println("confirmDate :"+confirmDate);
		System.out.println("detailList.getConfirmer() :"+detailList.getConfirmer());
		System.out.println("detailList.getBiko() :"+detailList.getBiko());
		System.out.println("detailList.getBanGo() :"+detailList.getBanGo());
		
		final byte[] blankValue = new byte[0];
		String sql = "UPDATE TBL_QA_LIST SET QA_FILE_TO_NM = ?, QA_FILE_TO = ?, QA_STATUS = ?,QA_KINKYUU = ?,QA_TO = ?,QA_QUESTION = ?,QA_ANSWER = ?,QA_LIMIT_DATE = ?,QA_ANSWER_DATE = ?,QA_CONFIRM_DATE = ?,QA_CONFIRMER = ?,QA_MEMO = ? WHERE QA_NO = ?";
		System.out.println(sql);
	    jdbcTemplate.execute(sql,
	    		new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
			protected void setValues(PreparedStatement ps,LobCreator lobCreator)
			throws SQLException{
				
				ps.setString(1, fileName);
				ps.setString(3, detailList.getStatus());
				ps.setString(4, detailList.getEmergencyDegree());
				ps.setString(5, detailList.getToIAWaSeSaKi());
				ps.setString(6, detailList.getSelBanGo());
				ps.setString(7, detailList.getNaiYo());
				ps.setString(8, limitDate);
				ps.setString(9, currentDate);
				ps.setString(10, confirmDate);
				ps.setString(11, detailList.getConfirmer());
				ps.setString(12, detailList.getBiko());
				ps.setString(13, detailList.getBanGo());
				
				if (file == null) {
					lobCreator.setBlobAsBytes(ps, 2, blankValue);//QA_FILE_TO
					System.out.println("file == null");
				}else {
					final FileSystemResource res = new FileSystemResource(file);
					System.out.println("FileSystemResource");
					byte[] mockImg = null;
					try {
						mockImg = FileCopyUtils.copyToByteArray(res.getFile());
						System.out.println("FileCopyUtils.copyToByteArray()");
					} catch (IOException e) {
						e.printStackTrace();
					}
					lobCreator.setBlobAsBytes(ps, 2, mockImg);//QA_FILE_TO
					System.out.println("setBlobAsBytes");
				}
				}});
	    System.out.println("End updateListdetailData()");
	}

	public LobHandler getLobHandler() {
		return lobHandler;
	}

	public void setLobHandler(LobHandler lobHandler) {
		this.lobHandler = lobHandler;
	}

	public String getFilename() {
		return filename;
	}

	public void setFilename(String filename) {
		this.filename = filename;
	}

	public InputStream getIs() {
		return is;
	}

	public void setIs(InputStream is) {
		this.is = is;
	}
}
